<?php

namespace App\V3\V3_2_7\Models;

use DateTimeInterface;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;

class StorageManageModel extends Model
{


    use HasFactory;

    protected $table = 'storage_manage';
    protected $dateFormat = 'U';//这个时间字段被为被格式化为UNIX 时间戳的形式存储

    public function productContent()
    {
        return self::hasMany(MaterielProductContentModel::class, 'product_id', 'type_id');
    }
    public function serialContent()
    {
        return self::hasMany(SerialNumberModel::class, 'storage_manage_id', 'id');
    }
    //toArray 时间转换问题
    public static function detail(array $data)
    {
        return self::select(
            'a.id',
            'd.materiel_name',
            'a.type_id',
            'd.type_name',
            'd.materiel_no',
            'd.materiel_img',
            'a.materiel_mean_cost',
            'a.storage_id',
            'e.storage_name',
            'e.storage_address',
            'a.in_storage_num',
            'a.alert_num',
            'a.virtual_storage_num',
            'c.id as f_class_id',
            'c.class_name as f_class_name',
            'c.class_no as f_class_no',
            'b.id as s_class_id',
            'b.class_name as s_class_name',
            'b.class_no as s_class_no',
            'd.remark',
            'a.shelf_id',
            'a.location_id',
            's.shelf_name',
            'l.location_name',
            'd.is_use_serial',
            'd.materiel_unit',
            'd.is_product_materiel',
            'a.delete_flag'
        )
            ->from('storage_manage as a')
            ->leftJoin('materiel_class as b', 'a.materiel_class_id', '=', 'b.id')
            ->leftJoin('materiel_class as c', 'c.id', '=', 'b.parent_id')
            ->leftJoin('materiel_type as d', 'd.id', '=', 'a.type_id')
            ->leftJoin('storage as e', 'e.id', '=', 'storage_id')
            ->leftJoin('storage_shelf as s','a.shelf_id','=','s.id')
            ->leftJoin('storage_shelf_location as l','a.location_id','=','l.id')
            ->where('a.id', $data['id'])->first();
    }

    protected function serializeDate(DateTimeInterface $date)
    {
        return $date->format('Y-m-d H:i:s');
    }

    //根据仓库id查询当前仓库中库存数最大的一条数据
    static public function getStorageManagerInfoByStorageId($id)
    {
        return self::where('storage_id', $id)
            ->where('delete_flag', 0)
            ->where('in_storage_num', '>', 0)
//            ->orderBy('in_storage_num', 'DESC')
            ->first();
    }

    static public function packageContentGetStorageInfo($data)
    {
        return self::select(
            'a.*',
            'c.id as f_id',
            'c.class_name',
            'c.class_no',
            'b.id as s_id',
            'b.class_name as s_class_name',
            'b.class_no as s_class_no',
        )
            ->from('storage_manage as a')
            ->leftJoin('materiel_class as b', 'b.id', '=', 'a.materiel_class_id')
            ->leftJoin('materiel_class as c', 'c.id', '=', 'b.parent_id')
            ->where('a.id', $data['storage_manage_id'])->first();
    }

    //指定套餐内容对应的在库信息列表(修改套餐内容用)
    static public function packageStorageList($parentId, $packageContentInfo)
    {
        return self::select(
            'a.id',
            'a.storage_id',
            'b.storage_name',
            'b.storage_address',
            'a.in_storage_num'
        )
            ->from('storage_manage as a')
            ->leftJoin('storage as b', 'b.id', '=', 'a.storage_id')
            ->where([
                'type_id' => $packageContentInfo['type_id'],
                'a.main_id' => $parentId,
                'a.delete_flag' => 0
            ])
            // ->where('a.id', '!=', $packageContentInfo['storage_manage_id'])
            ->get()->toArray();
    }

    public static function orderByCharset($parentId, $keyword, $condition, $data)
    {
        $query = self::query()
            ->select(
                'storage_manage.id',
                'storage_manage.type_id',
                'storage_manage.time_warning_flag',
//                'b.type_name',
//                'b.materiel_no',
//                'b.materiel_img',
//                'b.is_product_materiel',
//                'b.remark',
//                'b.is_use_serial',
//                'b.materiel_unit',
                'storage_manage.materiel_mean_cost',
                'storage_manage.storage_id',
                'e.storage_name',
                'e.storage_address',
                'storage_manage.in_storage_num',
                'storage_manage.alert_num',
                'storage_manage.virtual_storage_num',
                'd.id as f_class_id',
                'd.class_name as f_class_name',
                'd.class_no as f_class_no',
                'c.id as s_class_id',
                'c.class_name as s_class_name',
                'c.class_no as s_class_no',
                'storage_manage.shelf_id',
                'storage_manage.location_id',
                's.shelf_name',
                'l.location_name',
                'storage_manage.sort_flag'
            )
//            ->from('storage_manage')
//            ->leftJoin('materiel_type as b', 'b.id', '=', 'a.type_id')
            ->leftJoin('materiel_class as c', 'storage_manage.materiel_class_id', '=', 'c.id')
            ->leftJoin('materiel_class as d', 'd.id', '=', 'c.parent_id')
            ->leftJoin('storage as e', 'e.id', '=', 'storage_id')
            ->leftJoin('storage_shelf as s','storage_manage.shelf_id','=','s.id')
            ->leftJoin('storage_shelf_location as l','storage_manage.location_id','=','l.id')
//            ->leftJoin('serial_number as m', 'm.goods_id', '=', 'a.type_id')
            ->where($parentId)
            ->where('storage_manage.delete_flag', 0)
            ->where($condition);
//            ->where('m.package_id', 0);

        if(isset($data['storage_id']) && $data['storage_id'] != 0) {
            $query->where('storage_manage.storage_id', $data['storage_id']);
        }
        if(!empty($data['is_zero'])){
            $query->where('storage_manage.in_storage_num','!=', 0);
        }
        if(!empty($data['f_class_id'])){
            $secClassId = MaterielClassModel::query()
                ->where([
                    'delete_flag' => 0,
                    'main_id' => $data['main_id'],
                    'parent_id' => $data['f_class_id']
                ])->pluck('id');
            if(!empty($secClassId)){
                $query->whereIn('storage_manage.materiel_class_id',$secClassId->toArray());
            }
        }
        return $query->where(function($query) use ($keyword) {
            $query->whereHas('serialInfo', function($query) use ($keyword) {
                $query->where('serial_no', 'like', '%'.$keyword.'%')
                    ->where('package_id', 0);
            })
                ->orWhere('storage_manage.materiel_name', 'like', '%'.$keyword.'%')
                ->orWhere('storage_manage.type_name', 'like','%'.$keyword.'%')
                ->orWhere('storage_manage.materiel_no', 'like', '%'.$keyword.'%');

              /*  ->orWhere('c.class_name', 'like', '%'.$keyword.'%')
                ->orWhere('c.class_no', 'like', '%'.$keyword.'%')
                ->orWhere('d.class_name', 'like', '%'.$keyword.'%')
                ->orWhere('d.class_no', 'like', '%'.$keyword.'%');*/
//                    ->orWhere('m.serial_no', 'like', '%'.$keyword.'%');
        })
            ->orderByRaw("CONVERT(materiel_name USING gbk) COLLATE gbk_chinese_ci ".$data['order_by'])
            ->paginate($data['pageSize'],['*'], '',$data['currentPage']);
    }

    //库存查看列表
    static public function list($parentId, $keyword, $condition, $data, $sortFlag = [])
    {
            $query = self::select(
                'storage_manage.id',
                'b.materiel_name',
                'storage_manage.type_id',
                'b.type_name',
                'b.materiel_no',
                'b.materiel_img',
                'storage_manage.materiel_mean_cost',
                'storage_manage.storage_id',
                'e.storage_name',
                'e.storage_address',
                'storage_manage.in_storage_num',
                'storage_manage.alert_num',
                'storage_manage.virtual_storage_num',
                'd.id as f_class_id',
                'd.class_name as f_class_name',
                'd.class_no as f_class_no',
                'c.id as s_class_id',
                'c.class_name as s_class_name',
                'c.class_no as s_class_no',
                'b.is_product_materiel',
                'b.selling_price',
                'b.lower_selling_price',
                'b.remark',
                'storage_manage.shelf_id',
                'storage_manage.location_id',
                's.shelf_name',
                'l.location_name',
                'b.is_use_serial',
                'b.bar_code',
                'b.materiel_unit',
                'b.is_product_materiel',
                'storage_manage.sort_flag',
                'time_warning_flag',
                'b.two_materiel_name',
                'b.goods_supplier_name',
                'b.goods_client_name',
                'b.goods_supplier_name',
                'b.goods_client_name',
            )->from('storage_manage')
                ->leftJoin('materiel_type as b', 'b.id', '=', 'storage_manage.type_id')
                ->leftJoin('materiel_class as c', 'b.materiel_class_id', '=', 'c.id')
                ->leftJoin('materiel_class as d', 'd.id', '=', 'c.parent_id')
                ->leftJoin('storage as e', 'e.id', '=', 'storage_id')
                ->leftJoin('storage_shelf as s', 'storage_manage.shelf_id', '=', 's.id')
                ->leftJoin('storage_shelf_location as l', 'storage_manage.location_id', '=', 'l.id')
                ->where($parentId)
                ->where('storage_manage.delete_flag', 0)
                ->where('b.delete_flag', 0)
                ->where('c.delete_flag', 0)
                ->where('e.delete_flag', 0)
                ->where('s.status', 1)
                ->where('l.status', 1);
            if (!empty($sortFlag)) {
                $query->where($sortFlag);
            }
            if (!empty($condition)) {
                $query->where($condition);
            }
            if(!empty($data['f_class_id'])){
                $secClassId = MaterielClassModel::query()
                    ->where([
                    'delete_flag' => 0,
                    'main_id' => $data['main_id'],
                    'parent_id' => $data['f_class_id']
                ])->pluck('id');
                if(!empty($secClassId)){
                    $query->whereIn('b.materiel_class_id',$secClassId->toArray());
                }
            }
            if(!empty($data['is_zero'])){
                $query->where('storage_manage.in_storage_num','!=', 0);
            }
            if(!empty($data['storage_id']) && empty($data['shelf_id']) && empty($data['location_id'])){
                $query->where('storage_manage.storage_id', $data['storage_id']);
            }elseif (!empty($data['storage_id']) && !empty($data['shelf_id']) && empty($data['location_id'])){
                $query->where([
                    'storage_manage.storage_id' => $data['storage_id'],
                    'storage_manage.shelf_id' => $data['shelf_id'],
                    ]);
            }elseif (!empty($data['storage_id']) && !empty($data['shelf_id']) && !empty($data['location_id'])){
                $query->where([
                    'storage_manage.storage_id' => $data['storage_id'],
                    'storage_manage.shelf_id' => $data['shelf_id'],
                    'storage_manage.location_id' => $data['location_id'],
                ]);
            }

            if (!empty($keyword) || $keyword === 0) {
                if(strpos($keyword,'T-') !== false){
                    $keyword = explode('T-',$keyword);
                    $keyword = trim($keyword[1]);
                }
//                $query->where(function ($query)use($keyword){
//                    $query->orWhere('b.materiel_name', 'like', '%' . $keyword . '%');
//                    $query->orWhere('b.type_name', 'like', '%' . $keyword . '%');
//                    $query->orWhere('b.materiel_no', 'like', '%' . $keyword . '%');
//                });

                //$query->orWhere('b.materiel_no', 'like', '%' . $keyword . '%');
                //$query->orWhere('b.two_materiel_name', 'like', '%' . $keyword . '%');

                $data['sel_type'] = $data['sel_type']??1;
                switch ($data['sel_type']){
                    case 1://商品名称查询
                        if(strpos($keyword,'T-') !== false){
                            $keyword = explode('T-',$keyword);
                            $keyword = trim($keyword[1]);
                        }
                        $query->where('b.materiel_name', 'like', '%' . $keyword . '%');
                        break;
                    case 2://商品类型查询
                        $query->where('b.type_name', 'like', '%' . $keyword . '%');
                        break;
                    case 3://商品编号查询

                        $query->where('b.materiel_no', 'like', '%' . $keyword . '%');
                        break;
                    case 4://批次号查询
                        $query->leftJoin('storage_batch as g', 'g.storage_manage_id', '=', 'storage_manage.id')
                            ->where('g.batch_no', 'like', '%' . $keyword . '%');
                        break;
                    case 5://序列号查询
                        $query->leftJoin('serial_number as f', 'f.storage_manage_id', '=', 'storage_manage.id')
                            ->where('f.serial_no', 'like', '%' . $keyword . '%');
                        break;
                    default:
                        $query->where('b.materiel_name', 'like', '%' . $keyword . '%');
                        break;
                }
            };
            $query = $query->groupBy('storage_manage.id')->get();

            return !empty($query)?$query->toArray():[];
    }

    static public function storageManagelist($parentId, $keyword, $condition, $data, $sortFlag = [])
    {
        $param = '';
        switch ($sortFlag['sort_flag']){
            case 0:
                $param ='storage_manage_list_0';
                break;
            case 1:
                $param ='storage_manage_list_1';
                break;
            default:
                $sortFlag = [];
                $param ='storage_manage_list_3';
                break;
        }
            $query = self::select(
                'storage_manage.id',
                'b.materiel_name',
                'storage_manage.type_id',
                'b.type_name',
                'b.materiel_no',
                'b.materiel_img',
                'storage_manage.materiel_mean_cost',
                'storage_manage.storage_id',
                'e.storage_name',
                'e.storage_address',
                'storage_manage.in_storage_num',
                'storage_manage.alert_num',
                'storage_manage.virtual_storage_num',
                'd.id as f_class_id',
                'd.class_name as f_class_name',
                'd.class_no as f_class_no',
                'c.id as s_class_id',
                'c.class_name as s_class_name',
                'c.class_no as s_class_no',
                'b.is_product_materiel',
                'b.selling_price',
                'b.lower_selling_price',
                'b.remark',
                'storage_manage.shelf_id',
                'storage_manage.location_id',
                's.shelf_name',
                'l.location_name',
                'b.is_use_serial',
                'b.bar_code',
                'b.materiel_unit',
                'b.is_product_materiel',
                'storage_manage.sort_flag',
                'time_warning_flag'
            )->from('storage_manage')
                ->leftJoin('materiel_type as b', 'b.id', '=', 'storage_manage.type_id')
                ->leftJoin('materiel_class as c', 'b.materiel_class_id', '=', 'c.id')
                ->leftJoin('materiel_class as d', 'd.id', '=', 'c.parent_id')
                ->leftJoin('storage as e', 'e.id', '=', 'storage_id')
                ->leftJoin('storage_shelf as s', 'storage_manage.shelf_id', '=', 's.id')
                ->leftJoin('storage_shelf_location as l', 'storage_manage.location_id', '=', 'l.id')
                ->where($parentId)
                ->where('storage_manage.delete_flag', 0)
                ->where('b.delete_flag', 0)
                ->where('c.delete_flag', 0)
                ->where('e.delete_flag', 0)
                ->where('s.status', 1)
                ->where('l.status', 1);
            if (!empty($sortFlag)) {
                $query->where($sortFlag);
            }
            if (!empty($condition)) {
                $query->where($condition);
            }
            /*if(!empty($keyword)){
                $query->leftJoin('storage_batch as f','storage_manage.id','=','f.storage_manage_id');
                $query->where('f.delete_flag', 0);
            }*/
            if (isset($data['storage_id']) && $data['storage_id'] != 0) {
                $query->where('storage_manage.storage_id', $data['storage_id']);
            }

            $query = $query->where(function ($query) use ($keyword) {
                if (!empty($keyword)) {
                    $query->orWhere('b.materiel_name', 'like', '%' . $keyword . '%')
                        ->orWhere('b.type_name', 'like', '%' . $keyword . '%')
                        ->orWhere('b.materiel_no', 'like', '%' . $keyword . '%');
                }
            })->groupBy('storage_manage.id')->get();
            return !empty($query)?$query->toArray():[];

    }

    public function serialInfo(): \Illuminate\Database\Eloquent\Relations\HasMany
    {
        return self::hasMany(SerialNumberModel::class, 'storage_manage_id','id' );
    }

    //库存查看列表-套餐用
    static public function listForPackage($parentId, $keyword, $condition, $data)
    {
        $query = self::select(
            'a.id',
            'b.materiel_name',
            'a.type_id',
            'b.type_name',
            'b.materiel_no',
            'b.materiel_img',
            'a.materiel_mean_cost',
            'a.storage_id',
            'e.storage_name',
            'e.storage_address',
            'a.in_storage_num',
            'a.alert_num',
            'a.virtual_storage_num',
            'd.id as f_class_id',
            'd.class_name as f_class_name',
            'd.class_no as f_class_no',
            'c.id as s_class_id',
            'c.class_name as s_class_name',
            'c.class_no as s_class_no',
            'b.is_product_materiel',
            'b.remark',
            'a.shelf_id',
            'a.location_id',
            's.shelf_name',
            'l.location_name',
            'b.is_use_serial',
            'b.materiel_unit',
            'a.id as storage_manage_id',
        )
            ->from('storage_manage as a')
            ->leftJoin('materiel_type as b', 'b.id', '=', 'a.type_id')
            ->leftJoin('materiel_class as c', 'b.materiel_class_id', '=', 'c.id')
            ->leftJoin('materiel_class as d', 'd.id', '=', 'c.parent_id')
            ->leftJoin('storage as e', 'e.id', '=', 'storage_id')
            ->leftJoin('storage_shelf as s','a.shelf_id','=','s.id')
            ->leftJoin('storage_shelf_location as l','a.location_id','=','l.id')
            ->where('b.is_product_materiel', $data['is_product_materiel'])
            ->where('a.main_id', $parentId)
            ->where('a.delete_flag', 0)
            ->where($condition);

        if(isset($data['storage_id']) && $data['storage_id'] != 0) {
            $query->where('a.storage_id', $data['storage_id']);
        }

        return $query->where(function($query) use ($keyword) {
            $query->orWhere('b.materiel_name', 'like', '%'.$keyword.'%')
                ->orWhere('b.type_name', 'like','%'.$keyword.'%')
                ->orWhere('b.materiel_no', 'like', '%'.$keyword.'%')
                ->orWhere('c.class_name', 'like', '%'.$keyword.'%')
                ->orWhere('c.class_no', 'like', '%'.$keyword.'%')
                ->orWhere('d.class_name', 'like', '%'.$keyword.'%')
                ->orWhere('d.class_no', 'like', '%'.$keyword.'%');
        })
            ->orderBy('a.id', 'desc')
            ->paginate($data['pageSize'],['*'], '',$data['currentPage']);
    }

    //套餐内容库存信息
    static public function packageGetInfo($idArr)
    {
        return self::select(
            'a.id',
            'a.materiel_class_id',
            'a.type_id',
            'a.storage_id',
            'a.materiel_mean_cost',
            'b.class_name as materiel_class_name',
            'b.class_no as materiel_class_no',
            'c.materiel_name',
            'c.type_name',
            'c.materiel_no',
            'd.storage_name',
            'd.storage_address',
            'a.alert_num',
            'a.in_storage_num',
            'a.virtual_storage_num',
            'a.delete_flag',
            'c.selling_price',
            'c.cost'
        )
            ->from('storage_manage as a')
            ->leftJoin('materiel_class as b', 'b.id', '=', 'a.materiel_class_id')
            ->leftJoin('materiel_type as c', 'c.id', '=', 'a.type_id')
            ->leftJoin('storage as d', 'd.id', '=', 'a.storage_id')
            ->whereIn('a.id', $idArr)->get()->toArray();
    }

    //获取库存信息
    static public function getStorageManageInfo($typeId, $storageId, $classId, $mainId)
    {
        return self::where([
            'type_id' => $typeId,
            'storage_id' => $storageId,
            'materiel_class_id' => $classId,
            'main_id' => $mainId,
            'delete_flag' => 0
        ])->first();
    }

    //采购入库-获取库存信息
    static public function getInfo($classId, $typeId, $storageId, $mainId)
    {
        return self::where([
            'materiel_class_id' => $classId,
            'type_id' => $typeId,
            'storage_id' => $storageId,
            'main_id' => $mainId,
            'delete_flag' => 0
        ])->first();
    }

    static public function new_getInfo($classId, $typeId, $storageId, $mainId)
    {
        return self::where([
            'materiel_class_id' => $classId,
            'type_id' => $typeId,
            'storage_id' => $storageId,
            'main_id' => $mainId,
            'delete_flag' => 0
//            'shelf_id' => empty($shelf_id)?0:$shelf_id,
//            'location_id' => empty($location_id)?0:$location_id,

        ])->first();
    }

    //获取库存信息-废除对商品分类的验证
    static public function getInfos($data)
    {
        return self::where([
            'type_id' => $data['type_id'],
            'storage_id' => $data['storage_id'],
            'main_id' => $data['main_id'],
            'delete_flag' => 0
//            'shelf_id' => $data['shelf_id'],
//            'location_id' => $data['location_id'],
        ])->first();
    }

    public function batchList()
    {
        return self::hasMany(StorageBatchModel::class, 'storage_manage_id', 'id');
    }

    static public function materielBatchList($typeId, $data)
    {
        return self::select(
            'a.id',
            'in_storage_num',
            'type_id as materiel_type_id',
            'd.materiel_name',
            'd.type_name',
            'd.materiel_no',
            'd.materiel_img',
            'a.storage_id',
            'c.storage_name',
            'b.id as batch_id',
            'b.batch_num',
            'b.batch_left_num',
            'in_storage_num',
            'b.id as batch_id'
        )
            ->from('storage_manage as a')
            ->leftJoin('storage_batch as b', 'a.id', '=', 'b.storage_manage_id')
            ->leftJoin('storage as c', 'c.id', '=', 'a.storage_id')
            ->leftJoin('materiel_type as d', 'd.id', '=', 'a.type_id')
            ->where('a.type_id', $typeId)
            ->where('a.delete_flag', 0)
            ->where('batch_left_num', '!=', 0)
//            ->whereNotNull('b.id')
            ->distinct()
            ->paginate($data['pageSize'],['*'], '',$data['currentPage']);
    }
//今日预警数量
    static public function getTodayStorageWanringCount($condition, $startTime, $endTime,$storageIdArr = []){
        return self::query()
            ->from('storage_manage as a')
            ->leftJoin('storage as b','b.id','=','a.storage_id')
            ->where(function($query)use($condition,$storageIdArr){
                if(!empty($storageIdArr)){
                    $query->whereIn('storage_id',$storageIdArr);
                }else{
                    $query->where($condition);
                }
            })
            ->where([
                ['sort_flag', '=', 1],
                'a.delete_flag'=>0,
                'b.delete_flag'=>0
            ])
            ->whereBetween('a.created_at', [$startTime, $endTime])
            ->count();
    }

    //达到时间预警库存数量
    static public function getTodayStorageTimeWarningCount($condition, $startTime, $endTime, $storageIdArr =[]){
        return self::query()
            ->from('storage_manage as a')
            ->leftJoin('storage as b','b.id','=','a.storage_id')
            ->where(function($query)use($condition,$storageIdArr){
                if(!empty($storageIdArr)){
                    $query->whereIn('a.storage_id',$storageIdArr);
                }else{
                    $query->where($condition);
                }
            })
            ->where([
                ['time_warning_flag', '=', 1],
                'a.delete_flag'=>0,
                'b.delete_flag'=>0
            ])
            ->whereBetween('a.created_at', [$startTime, $endTime])
            ->count();
    }
}
